package com.ys.service.logistics.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ys.config.MyConfig;
import com.ys.dto.AddDto;
import com.ys.dto.EditDto;
import com.ys.entity.base.CheckByGrid;
import com.ys.entity.flow.YspModel;
import com.ys.entity.logistics.TlCustomsDeclaration;
import com.ys.entity.logistics.TlCustomsDeclarationInvoice;
import com.ys.entity.logistics.TlCustomsDeclarationItem;
import com.ys.entity.logistics.TlDecCustomSub;
import com.ys.entity.purchase.TcgInvoiceOffset;
import com.ys.entity.purchase.TcgInvoiceOffsetItem;
import com.ys.entity.sys.*;
import com.ys.enume.flow.FlowEnum;
import com.ys.enume.flow.YsProcInstEnum;
import com.ys.mapper.logistics.*;
import com.ys.mapper.purchase.TcgInvoiceOffsetItemMapper;
import com.ys.service.flow.YspTaskService;
import com.ys.service.logistics.TlCustomsDeclarationService;
import com.ys.service.sys.CommonApiService;
import com.ys.service.sys.UploadService;
import com.ys.util.CheckUtil;
import com.ys.util.DateUtil;
import com.ys.util.ScreenValueMergeStrategy;
import com.ys.util.shiro.ShiroUtils;
import com.ys.vo.Flow;
import com.ys.vo.R;
import org.apache.poi.ss.usermodel.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 报关单
 */
@Service("tlCustomsDeclarationService")
public class TlCustomsDeclarationServiceImpl implements TlCustomsDeclarationService {
	private static final Logger logger = LoggerFactory.getLogger(TlCustomsDeclarationServiceImpl.class);

	@Autowired
	private TlCustomsDeclarationMapper tlCustomsDeclarationMapper;

	@Autowired
	private TlCustomsDeclarationItemMapper tlCustomsDeclarationItemMapper;

	@Autowired
	private YspTaskService yspTaskService;

	@Autowired
	private CheckUtil checkUtil;

	@Autowired
	private LogisticsMapper logisticsMapper;

	@Autowired
	private TcgInvoiceOffsetItemMapper tcgInvoiceOffsetItemMapper;

	@Autowired
	private TlDecCustomSubMapper tlDecCustomSubMapper;

	@Autowired
	private CommonApiService<TlCustomsDeclaration> commonApiService;

	@Autowired
	private TlCustomsDeclarationInvoiceMapper tlCustomsDeclarationInvoiceMapper;


	private final Long GRID = 58L;

	/**
	 * 新增
	 */
	@Override
	@Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
	public R add(Map<String, Object> params, Long menuId) throws Exception{
		logger.info("新增传参:"+params);
		//参数验证
		String errorBack = checkParams(params, menuId);
		if(errorBack!=null && !"".equals(errorBack)){
			return R.error(errorBack);
		}
		TlCustomsDeclaration entity = JSON.parseObject(JSON.toJSONString(params), TlCustomsDeclaration.class);
		entity.setStatus(1);//状态
		entity.setEffectStock(0);//审批状态

		//获取制单人信息
		TsUserChange tsUserChange = new TsUserChange().setUserId(ShiroUtils.getUserId()).setStatus(1).queryFirst();
		if(tsUserChange!=null){
			entity.setCreateChangeId(tsUserChange.getId());//制单人变更信息
		}else {
			return R.error("未查询到制单人信息！");
		}

		//保存列表信息
		R saveItemRes = saveItem(params,entity);
		if(saveItemRes.getCode()==1){
			return saveItemRes;
		}

		//新增
		tlCustomsDeclarationMapper.insert(entity);

		YsMenu ysMenu = new YsMenu().setId(menuId).queryFirst();
		if (ysMenu.getFlowModelId().longValue() != 0L) {
			YspModel yspModel = new YspModel().setId(ysMenu.getFlowModelId()).queryFirst();
			String codeModel = yspModel.getCode();
			String createUserId = ShiroUtils.getUserId();
			yspTaskService.init(codeModel, entity.getBid(), createUserId);
		}
		//return R.ok(EditDto.closeAndReload());
		return R.ok(AddDto.gotoEdit("/web/logistics/tlCustomsDeclaration/toEdit?bid=" + entity.getBid()));
	}


	/**
	 * 修改
	 */
	@Override
	@Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
	public R update(Map<String, Object> params, Long menuId) throws Exception{
		logger.info("修改传参:"+params);
		//参数验证
		String errorBack = checkParams(params, menuId);
		if(errorBack!=null && !"".equals(errorBack)){
			return R.error(errorBack);
		}
		TlCustomsDeclaration entity = JSON.parseObject(JSON.toJSONString(params), TlCustomsDeclaration.class);
		// 验证状态
		YsProcInst ysProcInst = new YsProcInst().setBid(entity.getBid()).queryFirst();
		if (ysProcInst == null) {
			return R.error("审批数据不存在，无法编辑");
		} else if (ysProcInst.getStateFlow() != YsProcInstEnum.StateFlow.INIT.value) {
			return R.error("已流转，无法编辑");
		}

		//保存列表信息
		R saveItemRes = saveItem(params,entity);
		if(saveItemRes.getCode()==1){
			return saveItemRes;
		}
		tlCustomsDeclarationMapper.update(entity);
		return R.ok();
	}


	//保存子列表信息
	public R saveItem(Map<String, Object> params, TlCustomsDeclaration entity) throws Exception{
		//保存列表信息
		List<Map<String, Object>> subDataList = new ArrayList<>();
		if(params.get("subData")!=null){
			JSONArray subData = JSON.parseArray(params.get("subData").toString());
			CheckByGrid checkByGrid = checkParamsByGrid(subData,GRID,params);
			if(checkUtil.isNotNullByObject(checkByGrid.getErrMsg())){
				return R.error(checkByGrid.getErrMsg());
			}
			subDataList = checkByGrid.getSubDataList();
		}else {
			return R.error("列表信息为空！");
		}

		//先删除详情数据
		logisticsMapper.deleteTlCustomsDeclarationItemByBid(entity.getBid());

		List<Map<String, Object>> subDataListByNew = groupByDecCustomSubId(subDataList);
		//再新增
		for (int i = 0; i < subDataListByNew.size(); i++) {
			Map<String, Object> item = subDataListByNew.get(i);
			TlCustomsDeclarationItem itemInfo = JSON.parseObject(JSON.toJSONString(item), TlCustomsDeclarationItem.class);
			itemInfo.setStatus(1);
			itemInfo.setPbid(entity.getBid());
			//新增
			tlCustomsDeclarationItemMapper.insert(itemInfo);
		}
		saveInvoiceList(entity.getBid());


		return R.ok();
	}

	//保存发票列表信息
	public void saveInvoiceList(String bid)throws Exception{
		//先删除发票信息数据
		logisticsMapper.deleteTlCustomsDeclarationInvoiceByBid(bid);
		List<Map<String, Object>> itemInfoList = new TlCustomsDeclarationItem().setPbid(bid)
                .addLeftJoin("TL_DecCustomSub DS ON DS.Id = A.DecCustomSubId ")//报关子件表
                .addField("DS.NumDecCustom AS numDecCustom")//已报关数量
                .addLeftJoin("TCG_PurchaseAppointOrderProductComponent PAC ON PAC.Id = DS.ComponentId")//预约采购管理子件表
                .addLeftJoin("TP_ProductActiveComponent CS ON CS.Code = PAC.SubCode ")//listing库子件表
                .addField("CS.InvoiceModelNo AS invoiceModelNo")//发票型号
                .addField("CS.InvoiceName AS invoiceName")//发票品名
                .addLeftJoin("TCG_PurchaseAppointOrderItem PAOI ON PAOI.Id = PAC.Pid")//预约采购管理母件表
                .addLeftJoin("TCG_PurchaseContractItem PCI ON PCI.OrderProductComponentId = PAC.Id ")//采购合同管理子表
                .addLeftJoin("TCG_PurchaseContract PCIP ON PCIP.Bid = PCI.Pbid ")//采购合同管理
                .addLeftJoin("TB_CompanyInfo C ON C.Vid = PCIP.CompanyInfoVid ")//公司信息表
                .addField("C.Vname AS vname")//购买方公司名称
                .addLeftJoin("TCW_PartnerBankInfo PBI ON PBI.Id = PCIP.PartnerBankInfoId ")//合作商银行账户管理
                .addField("PBI.PartnerName AS partnerName")//供应方公司名称

				.addField("A.Id AS pid")
				.queryMap();

		//新增发票表信息
		List<Map<String, Object>> invoiceList = getInvoiceDeductionAddInvoice(itemInfoList);
		//获取报关单子表信息
        //List<TlCustomsDeclarationItem> itemList = new TlCustomsDeclarationItem().setPbid(bid).queryList();
        if(itemInfoList!=null && itemInfoList.size()>0){
            for(Map<String, Object> infoItem:itemInfoList){
                //根据TL_CustomsDeclarationInvoice表总数量是否和TL_CustomsDeclarationItem表一致
                List<Map<String, Object>> invoiceListByPid = invoiceList.stream()
                        .filter(item->item.get("pid").toString().equals(infoItem.get("id").toString()))
                        .collect(Collectors.toList());
                if(invoiceListByPid.size()>0){
                    int customsQuantity = Integer.parseInt(infoItem.get("customsQuantity").toString());
                    int deductQuantityByTotal = 0;
                    for(Map<String, Object> invoiceItem:invoiceListByPid){
                        int deductQuantity = Integer.parseInt(invoiceItem.get("deductQuantity").toString());
                        deductQuantityByTotal = deductQuantityByTotal + deductQuantity;
                    }
                    if(customsQuantity!=deductQuantityByTotal){
                        throw new IllegalArgumentException("报关单中还有信息未报关完全");
                    }
                }else {
                    throw new IllegalArgumentException("报关单中还有发票信息未匹配完全");
                }

            }

            //新增
            for (int i = 0; i < invoiceList.size(); i++) {
                Map<String, Object> item = invoiceList.get(i);
                TlCustomsDeclarationInvoice itemInfo = JSON.parseObject(JSON.toJSONString(item), TlCustomsDeclarationInvoice.class);
                itemInfo.setStatus(1);
                itemInfo.setPbid(bid);
                itemInfo.setDeductStatus(0);
                tlCustomsDeclarationInvoiceMapper.insert(itemInfo);
            }
        }else {
            throw new IllegalArgumentException("未查询到报关单详情信息");
        }

	}

	//删除
	@Override
	@Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
	public R del(Long id) throws Exception{
		if (id == null) {
			return R.error("ID不能为空");
		}
		TlCustomsDeclaration entity = new TlCustomsDeclaration().setId(id).queryFirst();
		if (entity.getEffectStock() == FlowEnum.EffectStock.EFFECTED.value) {
			return R.error("已生效数据无法删除");
		}
		// 验证是否制单人
		String loginUserId = ShiroUtils.getUserId();
		TsUserChange tsUserChange = new TsUserChange().setId(entity.getCreateChangeId()).queryFirst();
		if (!tsUserChange.getUserId().equals(loginUserId) && !"admin".equals(loginUserId)) {
			return R.error("非制单人，无法删除");
		}
		//删除
		tlCustomsDeclarationMapper.deleteTrue(id);
		//删除列表信息
		logisticsMapper.deleteTlCustomsDeclarationItemByBid(entity.getBid());
		//回退发票管理扣减的数量
		backInvoiceByBid(entity.getBid());
		//先删除发票信息数据
		logisticsMapper.deleteTlCustomsDeclarationInvoiceByBid(entity.getBid());
		//删除流程
		yspTaskService.flowDelete(entity.getBid(), loginUserId);
		return R.ok(EditDto.closeAndReload());
		//return R.ok();
	}


	/**
	 * 流转通过
	 */
	@Override
	@Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
	public R flowPass(Long id,String userId	) throws Exception {
		//获取单据信息
		TlCustomsDeclaration entity = new TlCustomsDeclaration().setId(id).queryFirst();
		if (entity == null) {
			return R.error("该单据不存在,请核实后再操作!");
		}
		YsProcInst ysProcInst = new YsProcInst().setBid(entity.getBid()).queryFirst();
		Flow f = yspTaskService.flowPass(entity.getBid(),userId);

		if (f.getCode() == Flow.Code.FAILED.value) {
			return R.error(f.getMsg());
		} else {
			// 若是制单流转，扣减发票数量
			if (ysProcInst.getStateFlow() == YsProcInstEnum.StateFlow.INIT.value) {
				//重新保存发票列表信息
				saveInvoiceList(entity.getBid());
				//扣减发票数量
				deductInvoiceByBid(entity.getBid());
			}
			if (f.getState() == Flow.State.FINISH.value) {
				return flowFinish(entity);
			}
			return R.ok();
		}
	}

	/**
	 * 流转结束
	 */
	public R flowFinish(TlCustomsDeclaration entity) throws Exception {

		YsProcInst ysProcInst = new YsProcInst().setBid(entity.getBid()).queryFirst();
		if (ysProcInst.getStateFlow() != YsProcInstEnum.StateFlow.FINISH.value) {
			return R.error("状态有误");
		}
		Date theDate = new Date();
		entity.setEffectStock(FlowEnum.EffectStock.EFFECTED.value);
		entity.setUpdateTime(theDate);

		//获取列表信息
		List<TlCustomsDeclarationItem> itemList = new TlCustomsDeclarationItem().setPbid(entity.getBid()).queryList();

		if(itemList!=null && itemList.size()>0){
			for(TlCustomsDeclarationItem item:itemList){
				//1、发票管理=》子件报关数量新增
				//获取发票管理数据

				/*
				TcgInvoiceOffsetItem tcgInvoiceOffsetItem = new TcgInvoiceOffsetItem().setId(item.getInvoiceOffsetItemId()).queryFirst();
				if(tcgInvoiceOffsetItem.getCustomsNum()==null){
					throw new IllegalArgumentException("未查询到对应的发票管理信息");
				}
				int offsetNum = tcgInvoiceOffsetItem.getOffsetNum();//发票本次核销数量、子件数量
				int customsNumByOld = tcgInvoiceOffsetItem.getCustomsNum();//报关数量
				int customsNumByNew = item.getCustomsQuantity() + customsNumByOld;//新增后的报关数量
				//新增后的报关数量不能超过 发票本次核销数量、子件数量
				if(customsNumByNew - offsetNum >0){
					throw new IllegalArgumentException("报关数量不能超过发票管理中对应的子件数量");
				}
				if(customsNumByNew == offsetNum){
					tcgInvoiceOffsetItem.setCustomsState(1);//报关完整性(0否、1是)
				}
				tcgInvoiceOffsetItem.setCustomsNum(customsNumByNew);//新增后的报关数量
				tcgInvoiceOffsetItemMapper.update(tcgInvoiceOffsetItem);


				 */




				//2、报关信息管理=》报关数量新增
				//获取报关信息子件表数据
				TlDecCustomSub tlDecCustomSub = new TlDecCustomSub().setId(item.getDecCustomSubId()).queryFirst();
				if(tlDecCustomSub.getNumDecCustom()==null){
					throw new IllegalArgumentException("未查询到对应的报关信息管理数据");
				}
				int numDecCustomByOld = tlDecCustomSub.getNumDecCustom();//报关数量
				int numDecCustomByNew = numDecCustomByOld + item.getCustomsQuantity();
				tlDecCustomSub.setNumDecCustom(numDecCustomByNew);
				tlDecCustomSubMapper.update(tlDecCustomSub);

			}
		}else {
			return R.error("未获取到对应的列表信息");
		}

		//修改信息
		tlCustomsDeclarationMapper.update(entity);
		return R.ok();

	}

	//打印
	@Override
	public R doPrint(String bid) throws Exception{
		//获取主表信息
		TlCustomsDeclaration entity = new TlCustomsDeclaration().setBid(bid).queryFirst();
		if(entity!=null && entity.getId()!=null){
			//获取子表信息
			List<Map<String, Object>> itemList = getItemListByBidNoInvoice(bid);
			if(itemList!=null && itemList.size()>0){
				//操作打印

				//打开模板
				String templateFilePath = MyConfig.getProfile() + "/template/store_template1.xls";
				String filePath = MyConfig.getProfile()+ "/" + bid+"_"+new Date().getTime()+".xlsx";
				ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(templateFilePath).build();


				WriteSheet writeSheet = EasyExcel.writerSheet().build();
				WriteSheet writeSheet1 = EasyExcel.writerSheet(1).build();
				WriteSheet writeSheet2 = EasyExcel.writerSheet(2).build();
				WriteSheet writeSheet3 = EasyExcel.writerSheet(3).registerWriteHandler(new ScreenValueMergeStrategy(1)).build();

				Map<String,Object> excelDataMap = new HashMap<String, Object>();
				excelDataMap.put("productionsales", entity.getProductionSales());//生产销售单位
				excelDataMap.put("productionsalesen", "Fuzhou Fuwoyang International Trade Co., Ltd");//销售单位英文名称
				excelDataMap.put("shippingmark", entity.getShippingMark());//唛头 => 标记唛码及备注
				excelDataMap.put("contractno", entity.getContractProtocolNo());//合同编号
				excelDataMap.put("createTime", DateUtil.getYYYY_MM_DD(entity.getCreateTime()) );//创建时间
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(entity.getCreateTime());//创建时间
				calendar.add(Calendar.DAY_OF_MONTH, 6);
				Date resultDate = calendar.getTime();
				excelDataMap.put("createTime1", DateUtil.getYYYY_MM_DD(resultDate));//装运期限 = 创建时间+6天
				excelDataMap.put("loadingport", entity.getLeaveCountryPort());//装船口岸 => 离境口岸

				Map<String, Object> itemFirst = itemList.get(0);

				//目的地地址 = 仓库名字 + 仓库地址1 + 城市 + ， + 省份 + 邮编 + 国家简称 + （仓库标识）
				String destinationaddress = ""+
						(itemFirst.get("addressName")!=null?itemFirst.get("addressName"):"")+" "+ //仓库名字
						(itemFirst.get("addressLine1")!=null?itemFirst.get("addressLine1"):"")+" "+ //仓库地址1
						(itemFirst.get("addressCity")!=null?itemFirst.get("addressCity"):"")+" ,"+ //城市
						(itemFirst.get("stateOrProvinceCode")!=null?itemFirst.get("stateOrProvinceCode"):"")+" "+ //省份
						(itemFirst.get("postalCode")!=null?itemFirst.get("postalCode"):"")+" "+ //邮编
						(itemFirst.get("countryCode")!=null?itemFirst.get("countryCode"):"")+" ("+ //国家简称
						(itemFirst.get("destinationFulfillmentCenterId")!=null?itemFirst.get("destinationFulfillmentCenterId"):"")+")"; //仓库标识

				excelDataMap.put("destinationaddress", destinationaddress);//目的地地址
				excelDataMap.put("foreignconsignee",entity.getAbroadShipper());//境外收货人
				excelDataMap.put("shippingtype", entity.getTransportMode());//运输方式
				excelDataMap.put("supervisionmethod",entity.getSuperviseMode());//监管方式
				excelDataMap.put("naturetaxation", entity.getTaxingAttribute());//征税性质
				excelDataMap.put("destinationcountry",entity.getArriveCountry());//运抵国（地区）
				excelDataMap.put("kindpackage",entity.getPackType());//包装种类
				excelDataMap.put("numbox",entity.getPackagesNum());//箱数 =》 件数
				excelDataMap.put("roughweight",entity.getGrossWeight());//整票毛重(kg)
				excelDataMap.put("netweight",entity.getNetWeight());//整票净重(kg)
				excelDataMap.put("termsdelivery",entity.getDealMode());//成交方式

				BigDecimal dw4 = BigDecimal.ZERO;
				BigDecimal dw5 = BigDecimal.ZERO;
				BigDecimal dw6 = BigDecimal.ZERO;
				List<Map<String, Object>> dataList = new ArrayList<>();
				List<Map<String, Object>> dataList1 = new ArrayList<>();
				List<Map<String, Object>> dataListNew = new ArrayList<>();

				//计算详情的总计
				for (Map<String, Object> item :itemList){
					BigDecimal length = BigDecimal.ZERO;
					BigDecimal width = BigDecimal.ZERO;
					BigDecimal height = BigDecimal.ZERO;
					if(checkUtil.isNotNullByObject(item.get("length"))) length = new BigDecimal(item.get("length").toString());
					if(checkUtil.isNotNullByObject(item.get("width"))) width = new BigDecimal(item.get("width").toString());
					if(checkUtil.isNotNullByObject(item.get("height"))) height = new BigDecimal(item.get("height").toString());

					BigDecimal mersurement = length //报关尺寸
							.multiply(width)
							.multiply(height)
							.divide(new BigDecimal(1000000), 2, BigDecimal.ROUND_HALF_UP);

					dw4 = dw4.add(new BigDecimal(item.get("goodsTotalWeight").toString()));//商品重量(kg)*发货数量
					dw5 = dw5.add(mersurement);//报关尺寸
					dw6 = dw6.add(new BigDecimal(item.get("saleTotalPrice").toString()));//报关总金额 累加

					//设置表格数据
					Map<String, Object> params1 = new HashMap<>();
					params1.put("spmcgg",item.get("subName"));//商品名称及规格型号 =》 商品名称
					params1.put("shippingmark", entity.getShippingMark());//唛头 => 标记唛码及备注
					params1.put("numbox",1);
					params1.put("quantityshipped",item.get("subBoxNum"));//发货数量 => 子件装箱数量
					params1.put("price2",item.get("saleUnitPrice"));//商品单价 =>销售单价
					params1.put("commoditycode", item.get("subCode"));//商品编码 = >子件商品编码
					params1.put("weightreal", item.get("singleWeight"));//实重 = >商品单重
					params1.put("netweight", item.get("netWeight"));//详情净重(kg)
					params1.put("mersurement",mersurement);//报关尺寸
					params1.put("nameunit",item.get("nameUnitCn"));//单位
					params1.put("bgprice",item.get("saleUnitPrice"));//报关单价 = >销售单价
					params1.put("bgtolprice",item.get("saleTotalPrice"));//报关总价 => 销售总价
					params1.put("sourcesupply",item.get("sourceAddress"));//货源地 => 境内货源地
					params1.put("destinationcountry",entity.getArriveCountry());//运抵国（地区）
					params1.put("zm",item.get("exemptionTax"));//征免
					params1.put("bt1","");
					params1.put("amount",(item.get("saleTotalPrice")));//报关总价 => 销售总价
					dataList.add(params1);

				}

				//获取子表信息->带发票信息
				//List<Map<String, Object>> itemListWithInvoice = getItemListByBid(bid);

				List<Map<String, Object>> itemListByGroups = new ArrayList<>();
				List<Map<String, Object>> itemListByNoNull = itemList.stream()
						.filter(item->item.get("invoiceModelNo")!=null && item.get("invoiceName")!=null
								&& item.get("vname")!=null && item.get("partnerName")!=null)
						//.filter(item->item.get("invoiceNo")!=null)
						.collect(Collectors.toList());
				if(itemListByNoNull.size()>0){
					//报关单相同的发票号码要合并
					Map<String, List<Map<String, Object>>> groupsBySubCode = itemListByNoNull.stream()
							.collect(Collectors.groupingBy(e->e.get("invoiceModelNo").toString() + "-" + e.get("invoiceName").toString()
									+ "-" + e.get("vname").toString()+ "-" + e.get("partnerName").toString()));
					//.collect(Collectors.groupingBy(e->e.get("invoiceNo").toString()));

				/*
				//报关单相同的子件编码要合并
				//根据子件编码去重
				Map<String, List<Map<String, Object>>> groupsBySubCode = itemList.stream()
						.collect(Collectors.groupingBy(e->e.get("subCode").toString()));
						//遍历，填入去重的商品信息
					groupsBySubCode.forEach((k, v) -> {
						BigDecimal deductQuantity = BigDecimal.ZERO;//发货数量 =>本次报关数量
						BigDecimal saleUnitPrice = BigDecimal.ZERO;//商品单价 =>销售单价
						BigDecimal saleTotalPrice = BigDecimal.ZERO;//报关总价 => 销售总价 = 销售单价*本次报关数量
						BigDecimal singleWeight = BigDecimal.ZERO;//单重
						if(v.size()>0){
							Map<String, Object> params = v.get(0);
							for(Map<String, Object> item:v){
								BigDecimal deductQuantityByItem =new BigDecimal(item.get("deductQuantity").toString());
								deductQuantity = deductQuantity.add(deductQuantityByItem);
								BigDecimal saleUnitPriceByItem = new BigDecimal(item.get("saleUnitPrice").toString());
								saleUnitPrice = saleUnitPrice.add(saleUnitPriceByItem);
								saleTotalPrice = saleTotalPrice.add(new BigDecimal(item.get("saleTotalPrice").toString()));
								singleWeight = singleWeight.add(new BigDecimal(item.get("singleWeight").toString()));
							}
							params.put("customsQuantity",deductQuantity);
							params.put("saleUnitPrice",saleUnitPrice);
							params.put("saleTotalPrice",saleTotalPrice);
							params.put("singleWeight",singleWeight);
							itemListByGroups.add(params);
						}
					});
				 */

					//遍历，填入去重的商品信息
					groupsBySubCode.forEach((k, v) -> {
						BigDecimal deductQuantity = BigDecimal.ZERO;//发货数量 =>本次报关数量=>扣减数量
						BigDecimal saleUnitPrice = BigDecimal.ZERO;//商品单价 =>销售单价
						BigDecimal saleTotalPrice = BigDecimal.ZERO;//报关总价 => 销售总价 = 销售单价*本次报关数量
						BigDecimal singleWeight = BigDecimal.ZERO;//单重
						if(v.size()>0){
							Map<String, Object> params = v.get(0);
							for(Map<String, Object> item:v){
								BigDecimal deductQuantityByItem =new BigDecimal(item.get("deductQuantity").toString());
								deductQuantity = deductQuantity.add(deductQuantityByItem);
								BigDecimal saleUnitPriceByItem = new BigDecimal(item.get("saleUnitPrice").toString());
								saleUnitPrice = saleUnitPrice.add(saleUnitPriceByItem);
								BigDecimal saleTotalPriceByItem = deductQuantityByItem.multiply(saleUnitPriceByItem);
								saleTotalPrice = saleTotalPrice.add(saleTotalPriceByItem);
								singleWeight = singleWeight.add(new BigDecimal(item.get("singleWeight").toString()));
							}
							params.put("customsQuantity",deductQuantity);
							params.put("saleUnitPrice",saleUnitPrice);
							params.put("saleTotalPrice",saleTotalPrice);
							params.put("singleWeight",singleWeight);
							itemListByGroups.add(params);
						}
					});
				}


				int i=0;
				for (Map<String, Object> item :itemListByGroups){
					i++;
					Map<String, Object> params1 = new HashMap<>();
					params1.put("numbox1",i);
					params1.put("quantityshipped",item.get("customsQuantity"));//发货数量 =>本次报关数量
					params1.put("commoditycode", item.get("subCodeMerge"));//商品编码 = >子件商品合并编码
					params1.put("bgtolprice",null);
					params1.put("price2",item.get("saleUnitPrice"));//商品单价 =>销售单价
					params1.put("commodityname",item.get("subName"));//商品名称
					params1.put("nameunit",item.get("nameUnitCn"));//单位
					params1.put("rnb","人民币");
					params1.put("cn",item.get("countryName"));//原产国
					params1.put("sourcesupply",item.get("sourceAddress"));//货源地 => 境内货源地
					params1.put("destinationcountry",entity.getArriveCountry());//运抵国（地区）
					params1.put("zm",item.get("exemptionTax"));//征免
					params1.put("bt1","");
					dataList1.add(params1);

					//第二条
					params1 = new HashMap<>();
					params1.put("numbox1",item.get("customsExplain"));//商品名称及规格型号 =》 商品报关说明
					params1.put("commoditycode", item.get("customsExplain"));//商品名称及规格型号 =》 商品报关说明
					params1.put("bt2",item.get("customsExplain"));//商品名称及规格型号 =》 商品报关说明
					params1.put("commodityname",item.get("customsExplain"));//商品名称及规格型号 =》 商品报关说明

					params1.put("price2",item.get("saleTotalPrice").toString());//报关总价 => 销售总价
					params1.put("quantityshipped",item.get("singleWeight").toString());//单重
					params1.put("nameunit","千克");//单位
					params1.put("cn","");
					params1.put("bt1","");
					params1.put("destinationcountry","");
					params1.put("sourcesupply","");
					params1.put("zm","");
					params1.put("rnb","人民币");
					dataList1.add(params1);
				}

				excelDataMap.put("dw1",itemList.size());
				excelDataMap.put("dw3",entity.getGrossWeight());//整票毛重(kg)
				//excelDataMap.put("dw4",dw4);//净重总计
				excelDataMap.put("dw4",entity.getNetWeight());//整票净重(kg)
				excelDataMap.put("dw5",dw5);//报关尺寸总计
				excelDataMap.put("amountTol",dw6);//报关总金额
				excelWriter.fill(excelDataMap,writeSheet);
				excelWriter.fill(excelDataMap,writeSheet1);
				excelWriter.fill(excelDataMap,writeSheet2);
				excelWriter.fill(excelDataMap,writeSheet3);

				FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
				excelWriter.fill(new FillWrapper(dataList), fillConfig, writeSheet);
				excelWriter.fill(new FillWrapper(dataList), fillConfig, writeSheet1);
				excelWriter.fill(new FillWrapper(dataList), fillConfig, writeSheet2);
				excelWriter.fill(new FillWrapper("data1",dataList1), fillConfig, writeSheet3);
				excelWriter.finish();

				// 导出Excel文件
				//String downloadPath = filePath;
				//File file = new File(downloadPath);
				//FileOutputStream fos = new FileOutputStream(file);
				//wb.write(fos);
				//fos.close();
				UploadService.UploadDto dto = commonApiService.uploadLocalToServer(filePath);
				//返回文件路径
				return R.ok(dto.getUrl());

			}else {
				return R.error("未获取到对应的报关单信息");
			}
		}else {
			return R.error("未获取到对应的报关单信息");
		}
		//return R.ok();
	}


	public TlCustomsDeclarationItem getSearchItemCom(TlCustomsDeclarationItem cond)throws Exception{
		cond
				.addLeftJoin("TL_DecCustomSub DS ON DS.Id = A.DecCustomSubId ")//报关子件表
				.addField("DS.NumDecCustom AS numDecCustom")//已报关数量

				.addField(" CONVERT(VARCHAR(255), A.DecCustomSubId) + DBP.BoxCode + FLD.Guid  AS onlyOneKey")//唯一值 = 报关信息子件表id + 装箱号 + 物流订单详情表guid
				//.addField("FL.Code + '' + A.FbaLogsticsOrderDetailGuid  AS onlyOneKey")//唯一值

				.addLeftJoin("TL_DecCustomBoxProduct DBP ON DS.DecCustomBoxProductId = DBP.Id ")//所属箱子产品表
				.addField("DBP.Num AS num")//母件装箱数量
				.addField("DBP.Pbid AS decCustomBid")//报关单号
				.addField("DBP.BoxCode AS boxCode")//装箱号

				.addLeftJoin("TL_DecCustom DC ON DC.Bid = DBP.Pbid ")//报关信息表

				.addLeftJoin("TP_FbaLogsticsOrder FL ON FL.Id = DC.FbaLogsticsOrderId ")//物流订单表
				.addField("FL.Code AS fbaLogsticsOrderCode")//物流订单号
				.addField("FL.WaybillNumber AS waybillNumber")//物流运单号
				.addField("FL.DestinationFulfillmentCenterId AS destinationFulfillmentCenterId")//仓库标识
				.addField("FL.NameLogsticsWay AS pNameLogsticsWay")//物流名称
				.addField("FL.CodeLogsticsServer AS codeLogsticsServer")//物流服务商

				.addLeftJoin("TP_FbaLogsticsOrderDetail FLD ON FLD.Guid = A.FbaLogsticsOrderDetailGuid")//物流订单详情表
				.addField("FLD.ShipmentId AS shipmentId")//FBA编号
				.addField("FLD.TrackingCode AS trackingCode")//货件追踪编号
				.addField("FLD.NameLogsticsWay AS nameLogsticsWay")//物流方式
				.addField("FLD.Code AS fbaLogsticsOrderDetailCode")//头程装箱单

				.addLeftJoin("TP_FbaLogsticsOrder_Box FLOB ON FLOB.LogsticsOrderDetailGuid = FLD.Guid")//物流订单箱子数据表
				.where("FLOB.BoxIndex = A.BoxIndex AND A.BidFbaBox = FLOB.CodeBox")
				.addField("FLOB.Length AS length")//长
				.addField("FLOB.Width AS width")//宽
				.addField("FLOB.Height AS height")//高

				.addLeftJoin("TP_AmazonAddress AAD ON AAD.DestinationFulfillmentCenterId = FL.DestinationFulfillmentCenterId ")//亚马逊仓库地址管理表
				.addField("AAD.CountryCode AS countryCode")//国家编码简称
				.addField("AAD.DistrictOrCounty AS districtOrCounty")//县市
				.addField("AAD.Name AS addressName")//仓库名称
				.addField("AAD.AddressLine1 AS addressLine1")//仓库地址1
				.addField("AAD.AddressLine2 AS addressLine2")//仓库地址2
				.addField("AAD.City AS addressCity")//城市
				.addField("AAD.StateOrProvinceCode AS stateOrProvinceCode")//县区级
				.addField("AAD.PostalCode AS postalCode")//邮编
				.addField("AAD.DestinationFulfillmentCenterId AS destinationFulfillmentCenterId")//仓库标识

				.addLeftJoin("TCG_PurchaseAppointOrderProductComponent PAC ON PAC.Id = DS.ComponentId")//预约采购管理子件表
				.addField("PAC.SubCode AS subCode")//子件编码
				.addField("PAC.SubName AS subName")//子件名称
				.addField("PAC.SubCodeMerge AS subCodeMerge")//子件合并编码

				.addLeftJoin("TCG_PurchaseAppointOrderItem PAOI ON PAOI.Id = PAC.Pid")//预约采购管理母件表
				.addField("PAOI.ProductCode AS productCode")//商品编码
				.addField("PAOI.ProductName AS productName")//商品名称

				.addLeftJoin("TCG_PurchaseContractItem PCI ON PCI.OrderProductComponentId = PAC.Id ")//采购合同管理子表
				.addField("PCI.SubBomNumber AS subBomNumber")//子件BOM数量
				.addField("PCI.SubPurchaseNum AS subPurchaseNum")//子件采购数量
				.addField("PCI.SubPurchasePrice AS subPurchasePrice")//子件采购单价

				.addLeftJoin("TP_ProductActiveComponent CS ON CS.Code = PAC.SubCode ")//listing库子件表
				.addField("CS.CustomsCode AS customsCode")//海关编码
				.addField("CS.ExemptionTax AS exemptionTax")//征免
				.addField("CS.CustomsExplain AS customsExplain")//商品报关说明
				.addField("CS.CountryName AS countryName")//原产国（地区）
				.addField("CS.SingleWeight AS singleWeight")//商品单重
				.addField("CS.NetWeight AS netWeight")//商品净重

				.addLeftJoin("TP_ProductActive CSP ON CS.Pcode = CSP.Code ")//listing库母件
				.addLeftJoin("TB_BaseinfoDwmc DBC ON DBC.Id = CSP.NameUnitId AND 1=1")//单位名称表
				.addField("DBC.NameUnitCn AS nameUnitCn")//单位名称（中）
				.addField("DBC.NameUnitEn AS nameUnitEn")//单位名称（英）

				.addField("DBP.Num * PCI.SubBomNumber AS subBoxNum")//子件装箱数量 = 母件装箱数量*子件BOM数量

				.addLeftJoin("TCG_PurchaseContract PCIP ON PCIP.Bid = PCI.Pbid ")//采购合同管理
				.addLeftJoin("TB_CompanyInfo C ON C.Vid = PCIP.CompanyInfoVid ")//公司信息表
				.addField("C.Vname AS vname")//购买方公司名称

				.addLeftJoin("TCW_PartnerBankInfo PBI ON PBI.Id = PCIP.PartnerBankInfoId ")//合作商银行账户管理
				.addField("PBI.PartnerName AS partnerName")//供应方公司名称
		;
		return cond;
	}

	//根据bid，获取子表信息-》无发票相关信息
	public List<Map<String, Object>> getItemListByBidNoInvoice(String bid) throws Exception{
		List<Map<String, Object>> itemList = getSearchItemCom(new TlCustomsDeclarationItem())
				.setPbid(bid)
				.queryMap();

		return itemList;
	}
	//根据bid，获取子表信息
	@Override
	public List<Map<String, Object>> getItemListByBid(String bid) throws Exception{
		List<Map<String, Object>> itemList = getSearchItemCom(new TlCustomsDeclarationItem())

				.addLeftJoin("TL_CustomsDeclarationInvoice SI ON SI.Pid = A.Id ")//发票扣减信息表
				.addField("SI.DeductQuantity AS deductQuantity")//发票扣减数量
				.addField("SI.DeductBeforeQuantity AS deductBeforeQuantity")//发票扣减前数量(发票可报关数量)
				.addField("SI.DeductQuantityByNo AS deductQuantityByNo")//报关未扣减数量
				.addField("SI.NumDecCustomByInvoice AS numDecCustomByInvoice")//发票已报关数量
				.addField("SI.InvoiceSurplusNum AS invoiceSurplusNum")//发票剩余数量
                .addField("SI.DeductQuantityByAfter AS deductQuantityByAfter")//报关扣减后数量


				.addLeftJoin("TCG_InvoiceOffsetItem IOS ON IOS.Id = SI.InvoiceOffsetItemId ")//发票管理子表
				.addField("IOS.InvoiceModelNo AS invoiceModelNo")//发票型号
				.addField("IOS.InvoiceName AS invoiceName")//发票品名
				.addField("IOS.OffsetNum - IOS.CustomsNum AS invoiceSurplusNumByInvoice")//发票剩余报关数量
				.addField("IOS.OffsetNum AS offsetNum")//发票数量

				.addLeftJoin("TCG_InvoiceOffset I ON I.Bid = IOS.Pbid ")//发票管理
				.addField("I.SourceAddress AS sourceAddress")//货源所在地

				.addLeftJoin("TCG_InvoiceRegist IR ON IR.InvoiceOffsetId = I.Id AND IR.EffectStock = 1")//发票登记单
				.addField("IR.InvoiceNo AS invoiceNo")//发票号码

				.setPbid(bid)
				.queryMap();

		return itemList;
	}


	/**
	 * 汇总发票扣减列表
	 * @param conditionInfo
	 * 		发票查询条件信息，必须包含：
	 * 		发票型号（invoiceModelNo）、发票品名（invoiceName）、报关信息子件表id（decCustomSubId）
	 * 		购买方公司名称（vname）、销售方公司名称（partnerName）、本次报关数量（customsQuantity）
	 * @return 发票型号（invoiceModelNo）、发票品名（invoiceName）、报关信息子件表id（decCustomSubId）
	 * 		购买方公司名称（vname）、销售方公司名称（partnerName）、发票号码（invoiceNo）、境内货源地（sourceAddress）
	 * 		发票剩余报关数量（invoiceSurplusNum）、扣减数量(deductQuantity)、扣减前数量(deductBeforeQuantity)
	 * 		报关未扣减数量(不够扣减)(deductQuantityByNo)、发票管理子表id（invoiceOffsetItemId）、发票已报关数量（numDecCustomByInvoice）
     * 	deductQuantityByAfter
	 */
	@Override
	@Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
	public List<Map<String, Object>> getInvoiceDeduction(List<Map<String, Object>> conditionInfo) throws Exception {
		List<Map<String, Object>> res = new ArrayList<>();
        logger.info("将为空的信息去除前conditionInfo:\n"+conditionInfo);
		//将为空的信息去除
		List<Map<String, Object>> conditionInfoNoNull = conditionInfo.stream()
				.filter(item->checkUtil.isNotNullByObject(item.get("invoiceModelNo"))
						&& checkUtil.isNotNullByObject(item.get("invoiceName"))
						&& checkUtil.isNotNullByObject(item.get("decCustomSubId"))
						&& checkUtil.isNotNullByObject(item.get("vname"))
						&& checkUtil.isNotNullByObject(item.get("partnerName"))
						&& checkUtil.isNotNullByObject(item.get("customsQuantity"))
						&& new BigDecimal(item.get("customsQuantity").toString()).compareTo(BigDecimal.ZERO)>0 )
				.collect(Collectors.toList());
		logger.info("将为空的信息去除conditionInfoNoNull:\n"+conditionInfoNoNull);
		if(conditionInfoNoNull.size()>0){
			//去重
			Map<String, List<Map<String, Object>>> groupsByCode = conditionInfo.stream()
					.collect(Collectors.groupingBy(e->e.get("invoiceModelNo")+"_"+e.get("invoiceName")+"_"+e.get("vname")+"_"+e.get("partnerName")));
			List<Map<String, Object>> conditionInfoList = new ArrayList<>();
			//遍历，填入去重的条件信息
			groupsByCode.forEach((k, v) -> conditionInfoList.add(v.get(0)));
			logger.info("去重conditionInfoList:\n"+conditionInfoList);
			if(conditionInfoList.size()>0){
				//遍历、查询符合条件的发票信息列表；
				List<Map<String, Object>> invoiceInfoList = new ArrayList<>();
				for(Map<String, Object> conditionItem:conditionInfoList){
					String invoiceModelNo = conditionItem.get("invoiceModelNo").toString();//发票型号
					String invoiceName = conditionItem.get("invoiceName").toString();//发票品名
					String vname = conditionItem.get("vname").toString();//购买方公司名称
					String partnerName = conditionItem.get("partnerName").toString();//销售方公司名称
					//获取符合条件的发票信息列表
					List<Map<String, Object>> invoiceInfoByCondition = getInvoiceInfoByCondition(invoiceModelNo,invoiceName,vname,partnerName);
					if(invoiceInfoByCondition!=null && invoiceInfoByCondition.size()>0){
						invoiceInfoList.addAll(invoiceInfoByCondition);
					}
				}
				logger.info("查询符合条件的发票信息列表invoiceInfoList:\n"+invoiceInfoList);
				//遍历，进行发票的分发
				if(invoiceInfoList.size()>0){
					for(Map<String, Object> infoItem:conditionInfoNoNull){
						String invoiceModelNo = infoItem.get("invoiceModelNo").toString();//发票型号
						String invoiceName = infoItem.get("invoiceName").toString();//发票品名
						String vname = infoItem.get("vname").toString();//购买方公司名称
						String partnerName = infoItem.get("partnerName").toString();//销售方公司名称

						BigDecimal customsQuantity = new BigDecimal(infoItem.get("customsQuantity").toString());//本次报关数量
						BigDecimal deductQuantityByNo = new BigDecimal(infoItem.get("customsQuantity").toString());//本次报关数量、未扣减数量
						Long decCustomSubId = Long.parseLong(infoItem.get("decCustomSubId").toString());//报关信息子件表id
						//根据条件，获取对应的发票信息
						List<Map<String, Object>> invoiceInfoListByCondition = invoiceInfoList.stream()
								.filter(item->item.get("invoiceModelNo").toString().equals(invoiceModelNo)
										&& item.get("invoiceName").toString().equals(invoiceName)
										&& item.get("vname").toString().equals(vname)
										&& item.get("partnerName").toString().equals(partnerName)
								)
								.collect(Collectors.toList());
						logger.info("根据条件，获取对应的发票信息invoiceInfoListByCondition:\n"+invoiceInfoListByCondition);
						if(invoiceInfoListByCondition.size()>0){
							//遍历发票信息，进行扣减操作，直到全部扣减
							for (Map<String, Object> invoiceItem:invoiceInfoListByCondition){
								Map<String,Object> newInfo = new HashMap<>();

								BigDecimal offsetNum = new BigDecimal(invoiceItem.get("offsetNum").toString());//发票本次核销数量、子件数量
								BigDecimal deductQuantity = BigDecimal.ZERO;//扣减数量
								Long itemId = Long.parseLong(invoiceItem.get("itemId").toString());//发票子表id
								BigDecimal deductBeforeQuantity = new BigDecimal(invoiceItem.get("invoiceSurplusNum").toString());//扣减前数量
								//填入数据
								newInfo.put("invoiceModelNo",invoiceModelNo);//发票型号
								newInfo.put("invoiceName",invoiceName);//发票品名
								newInfo.put("decCustomSubId",decCustomSubId);//报关信息子件表id
								newInfo.put("vname",vname);//购买方公司名称
								newInfo.put("partnerName",partnerName);//销售方公司名称
								newInfo.put("invoiceNo",invoiceItem.get("invoiceNo"));//发票号码
								newInfo.put("sourceAddress",invoiceItem.get("sourceAddress"));//境内货源地
								newInfo.put("deductBeforeQuantity",deductBeforeQuantity);//扣减前数量
								newInfo.put("invoiceOffsetItemId",itemId);//发票管理子表id
								newInfo.put("offsetNum",offsetNum);//发票数量
                                newInfo.put("deductQuantityByNo",deductQuantityByNo);//未扣减数量
                                newInfo.put("numDecCustomByInvoice",offsetNum.subtract(deductBeforeQuantity));//发票已报关数量

								logger.info("******************************************************************");
								logger.info("报关*信息子件表id："+decCustomSubId);
								logger.info("发票*子表id："+itemId);
								logger.info("发票*扣减前数量："+deductBeforeQuantity);
								logger.info("报关*未扣减数量："+deductQuantityByNo);
								if(deductBeforeQuantity.compareTo(BigDecimal.ZERO)==0){//发票数量已经扣减到0了，就跳过
									continue;
								}
								if(deductQuantityByNo.compareTo(deductBeforeQuantity)<=0){//足够扣减
									//计算数量
									BigDecimal invoiceSurplusNum = deductBeforeQuantity.subtract(deductQuantityByNo);//发票剩余报关数量
									deductQuantity = deductQuantityByNo;//扣减数量
									deductQuantityByNo = BigDecimal.ZERO;//未扣减数量
									//填入数据
									newInfo.put("invoiceSurplusNum",invoiceSurplusNum);//发票剩余报关数量
									newInfo.put("deductQuantity",deductQuantity);//扣减数量
									newInfo.put("deductQuantityByAfter",deductQuantityByNo);//未扣减数量
									//newInfo.put("numDecCustomByInvoice",offsetNum.subtract(invoiceSurplusNum));//发票已报关数量

									logger.info("足够扣减");
									logger.info("发票*剩余报关数量："+invoiceSurplusNum);
									logger.info("报关*扣减数量："+deductQuantity);

									res.add(newInfo);
									//修改invoiceInfoList列表中发票剩余报关数量
									invoiceInfoList = updateInvoiceSurplusNum(invoiceInfoList,itemId,invoiceSurplusNum);
									break;
								}else {//不够扣减

									//计算数量
									BigDecimal invoiceSurplusNum = BigDecimal.ZERO;//发票剩余报关数量
									deductQuantity = deductBeforeQuantity;//扣减数量
									deductQuantityByNo = deductQuantityByNo.subtract(deductBeforeQuantity);//未扣减数量

									logger.info("不够扣减");
									logger.info("发票*剩余报关数量："+invoiceSurplusNum);
									logger.info("报关*扣减数量："+deductQuantity);

									//填入数据
									newInfo.put("invoiceSurplusNum",invoiceSurplusNum);//发票剩余报关数量
									newInfo.put("deductQuantity",deductQuantity);//扣减数量
									newInfo.put("deductQuantityByAfter",deductQuantityByNo);//未扣减数量
									//newInfo.put("numDecCustomByInvoice",offsetNum.subtract(invoiceSurplusNum));//发票已报关数量
									res.add(newInfo);
									//修改invoiceInfoList列表中发票剩余报关数量
									invoiceInfoList = updateInvoiceSurplusNum(invoiceInfoList,itemId,invoiceSurplusNum);
								}
							}
						}
					}
				}

			}

		}

		return res;

	}



	//同getInvoiceDeduction，将连同无发票信息的一同返回
	@Override
	@Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
	public List<Map<String, Object>> getInvoiceDeductionAddInvoice(List<Map<String, Object>> itemList) throws Exception {
		List<Map<String, Object>> res = new ArrayList<>();
		if(itemList!=null && itemList.size()>0){
			List<Map<String, Object>> itemListByGroup = groupByDecCustomSubId(itemList);
			List<Map<String, Object>> invoiceList = getInvoiceDeduction(itemListByGroup);
			if(invoiceList!=null && invoiceList.size()>0){
				for(Map<String, Object> item:itemListByGroup){
					boolean isInvoice = false;
					List<Map<String, Object>> invoiceListBySubId = invoiceList.stream()
							.filter(e->e.get("decCustomSubId").toString().equals(item.get("decCustomSubId").toString()))
							.collect(Collectors.toList());
					if(invoiceListBySubId.size()>0){
						isInvoice = true;
						for(Map<String, Object> invoiceItem:invoiceListBySubId){
							item.forEach((k, v) -> {
								invoiceItem.put(k,v);
							});
							res.add(invoiceItem);
						}
					}
					if(!isInvoice){
						res.add(item);
					}
				}

			}else {
				return itemList;
			}
		}
		return res;

	}


	//根据：报关信息子件表id（decCustomSubId）去重
	public List<Map<String, Object>> groupByDecCustomSubId(List<Map<String, Object>> itemList) throws Exception {
		List<Map<String, Object>> itemListByNew = new ArrayList<>();
		Map<String, List<Map<String, Object>>> groupsByCode = itemList.stream()
				.collect(Collectors.groupingBy(e->e.get("decCustomSubId").toString()));
		//遍历，填入去重的条件信息
		groupsByCode.forEach((k, v) -> itemListByNew.add(v.get(0)));
		return itemListByNew;
	}



	//修改列表中发票剩余报关数量
	public List<Map<String, Object>> updateInvoiceSurplusNum(List<Map<String, Object>> invoiceInfoList,Long itemId,BigDecimal invoiceSurplusNum)throws Exception{

		for(int i=0;i<invoiceInfoList.size();i++){
			Long itemIdByIndex = Long.parseLong(invoiceInfoList.get(i).get("itemId").toString());//发票子表id
			if(itemId.equals(itemIdByIndex)){
				Map<String, Object> newInfo = invoiceInfoList.get(i);
				newInfo.put("invoiceSurplusNum",invoiceSurplusNum);
				invoiceInfoList.set(i, newInfo);
				break;
			}
		}
		return invoiceInfoList;
	}

	public List<Map<String, Object>> getInvoiceInfoByCondition(String invoiceModelNo,String invoiceName,String vname,String partnerName)throws Exception{
		List<Map<String, Object>> invoiceInfoByCondition = new TcgInvoiceOffset()
				.addLeftJoin("TB_CompanyInfo C ON C.Vid = A.CompanyVid ")//公司信息表
				.addField("C.Vname AS vname")//购买方公司名称
				.addLeftJoin("TCW_PartnerBankInfo PBI ON PBI.Id = A.PartnerBankInfoId ")//合作商银行账户管理
				.addField("PBI.PartnerName AS partnerName")//供应方公司名称

				.addLeftJoin("TCG_InvoiceRegist IR ON IR.InvoiceOffsetId = A.Id AND IR.EffectStock = 1")//发票登记单
				.addField("IR.InvoiceNo AS invoiceNo")//发票号码

				.addLeftJoin("TCG_InvoiceOffsetItem S ON S.Pbid = A.Bid")
				.addField("S.Id AS itemId")//子表id
				.addField("S.InvoiceModelNo AS invoiceModelNo")//发票型号
				.addField("S.InvoiceName AS invoiceName")//发票品名
				.addField("S.OffsetNum AS offsetNum")//发票数量

				.addField("S.OffsetNum - S.CustomsNum AS invoiceSurplusNum")//发票剩余报关数量

				.setEffectStock(1)
				.where("A.InvoiceState !=2")//发票状态(0否、1是、2删除/取消)
				.where("S.CustomsState = 0 ")//报关完整性(0否、1是)
				//锁定四个条件
				.where("S.InvoiceModelNo = '"+invoiceModelNo+"' AND S.InvoiceName = '"+invoiceName+"' AND C.Vname = '"+vname+"' AND PBI.PartnerName = '" + partnerName +"'")
				.setOrderby("A.UpdateTime ASC ")//按照审批时间顺序排序
				.queryMap();
		return invoiceInfoByCondition;
	}

	//回退发票管理中的扣减信息
	public R backInvoiceByBid(String bid)throws Exception{
		List<TlCustomsDeclarationInvoice> invoiceList = new TlCustomsDeclarationInvoice().setPbid(bid).setDeductStatus(1).queryList();
		if(invoiceList!=null && invoiceList.size()>0){
			for(TlCustomsDeclarationInvoice item:invoiceList){
				TcgInvoiceOffsetItem invoiceOffsetItem = new TcgInvoiceOffsetItem().setId(item.getInvoiceOffsetItemId()).queryFirst();
				if(invoiceOffsetItem!=null && invoiceOffsetItem.getStatus()!=null){
					int offsetNum = invoiceOffsetItem.getOffsetNum();//发票可核销总数量
					int deductQuantity = item.getDeductQuantity();//发票扣减数量
					int customsNumByNew = offsetNum - deductQuantity;//回退后的报关数量
					invoiceOffsetItem.setCustomsState(0);//报关完整性(0否、1是)
					invoiceOffsetItem.setCustomsNum(customsNumByNew);//填入
					tcgInvoiceOffsetItemMapper.update(invoiceOffsetItem);
					item.setDeductStatus(0);
					tlCustomsDeclarationInvoiceMapper.update(item);//修改状态
				}else {
					throw new IllegalArgumentException("未查询到对应的发票管理信息");
				}
			}
		}
		return R.ok();
	}


	//扣减发票管理中的
	public R deductInvoiceByBid(String bid)throws Exception{
		List<TlCustomsDeclarationInvoice> invoiceList = new TlCustomsDeclarationInvoice().setPbid(bid).setDeductStatus(0).queryList();
		if(invoiceList!=null && invoiceList.size()>0){
			for(TlCustomsDeclarationInvoice item:invoiceList){
				TcgInvoiceOffsetItem invoiceOffsetItem = new TcgInvoiceOffsetItem().setId(item.getInvoiceOffsetItemId()).queryFirst();
				if(invoiceOffsetItem!=null && invoiceOffsetItem.getStatus()!=null){

					int offsetNum = invoiceOffsetItem.getOffsetNum();//发票可核销总数量
					int deductQuantity = item.getDeductQuantity();//发票扣减数量
					int customsNum = invoiceOffsetItem.getCustomsNum();//已报关数量
					int customsNumByNew = customsNum + deductQuantity;//新增后的报关数量
					//新增后的报关数量不能超过 发票本次核销数量、子件数量
					if(customsNumByNew - offsetNum >0){
						throw new IllegalArgumentException("发票扣减数量不能超过发票管理中对应的子件数量");
					}
					if(customsNumByNew == offsetNum){
						invoiceOffsetItem.setCustomsState(1);//报关完整性(0否、1是)
					}
					invoiceOffsetItem.setCustomsNum(customsNumByNew);//新增后的报关数量
					tcgInvoiceOffsetItemMapper.update(invoiceOffsetItem);
					item.setDeductStatus(1);
					tlCustomsDeclarationInvoiceMapper.update(item);//修改状态
				}else {
					throw new IllegalArgumentException("未查询到对应的发票管理信息");
				}
			}
		}else {
			throw new IllegalArgumentException("未查询到对应的发票扣减信息");
		}
		return R.ok();
	}



	//流转驳回
	@Override
	@Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
	public R flowRefuse(String bid, String reason,String userId) throws Exception{

		R r = yspTaskService.flowRefuse(bid, reason, userId);

		if (r.getCode() == R.Code.SUCCESS.value) {
			YsProcInst ysProcInst = new YsProcInst().setBid(bid).queryFirst();
			// 若退回到制单状态，则回退发票管理中的扣减信息
			if (ysProcInst.getStateFlow() == YsProcInstEnum.StateFlow.INIT.value) {
				backInvoiceByBid(bid);
			}
		}
		return r;
	}


	//流转退回
	@Override
	@Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
	public R flowInit(String bid, String reason,String userId) throws Exception{
		R r = yspTaskService.flowInit(bid, reason, userId);
		if (r.getCode() == R.Code.SUCCESS.value) {
			YsProcInst ysProcInst = new YsProcInst().setBid(bid).queryFirst();
			// 若退回到制单状态，则回退发票管理中的扣减信息
			if (ysProcInst.getStateFlow() == YsProcInstEnum.StateFlow.INIT.value) {
				backInvoiceByBid(bid);
			}
		}
		return r;
	}




	/**
	 * 参数验证
	 */
	public String checkParams(Map<String, Object> params, Long menuId) throws Exception {
		//获取pc字段数据
		List<YsCol> listField = new YsCol()
				.where("A.MenuId = "+menuId)
				.setOrderby("A.SortNo ASC")
				.queryList();
		//验证必填字段的非空；其他字段的长度限制、类型
		String backMsg = checkUtil.checkTableColLengthAndDataType("TL_CustomsDeclaration",listField,params,ysColItem->ysColItem.getFormEditRequired()!=null && ysColItem.getFormEditRequired() == 1);

		return backMsg;
	}


	/**
	 * 子表参数验证
	 */
	public CheckByGrid checkParamsByGrid(JSONArray subDataArray, Long gridId,Map<String, Object> params) throws Exception {
		CheckByGrid checkByGrid = new CheckByGrid();
		//获取pc字段数据
		List<YsGridCol> listField = new YsGridCol()
				.setGridId(gridId)
				.setOrderby("A.SortNo ASC")
				.queryList();
		String backMsg = "";
		List<Map<String, Object>> subDataList = new ArrayList<>();

		if(subDataArray!=null && subDataArray.size()>0){
			for (int i = 0; i < subDataArray.size(); i++) {
				JSONObject jsonObject = subDataArray.getJSONObject(i);
				Map<String, Object> paramsNewByRecord = new HashMap<>();
				for (String key : jsonObject.keySet()) {
					paramsNewByRecord.put(key, jsonObject.get(key));
				}
				subDataList.add(paramsNewByRecord);
			}
			//验证
			for(int i = 0;i<subDataList.size();i++){
				String backMsgByRow = "";
				Map<String, Object> subData = subDataList.get(i);
				//验证必填字段的非空；其他字段的长度限制、类型
				backMsgByRow += checkUtil.checkTableColLengthAndDataTypeByGrid(
						"TL_CustomsDeclarationItem",
						listField,
						subData,
						ysColItem->
								ysColItem.getCode().equals("decCustomSubId")
										|| ysColItem.getCode().equals("subCode")
										|| ysColItem.getCode().equals("customsQuantity")
										|| ysColItem.getCode().equals("goodsTotalWeight")
										|| ysColItem.getCode().equals("saleUnitPrice")
										|| ysColItem.getCode().equals("saleTotalPrice")
										|| ysColItem.getCode().equals("invoiceOffsetItemId")

				);
				if(checkUtil.isNotNullByObject(subData.get("customsQuantity"))){
					if(!checkUtil.isInt(subData.get("customsQuantity").toString())){
						backMsgByRow += "本次报关数量应是整数；";
					}else {
						if(Integer.parseInt(subData.get("customsQuantity").toString())<=0){
							backMsgByRow += "本次报关数量应大于0；";
						}
						/*
						if(Integer.parseInt(subData.get("customsQuantity").toString()) >Integer.parseInt(subData.get("invoiceSurplusNum").toString())){
							backMsgByRow += "本次报关数量应≤发票剩余报关数量；";
						}
						 */
					}
				}

				if(checkUtil.isNotNullByObject(backMsgByRow)){
					backMsg += "列表第" + (i+1) + "行，" + backMsgByRow;
				}
			}
		}else{
			backMsg = "列表信息为空";
		}
		checkByGrid.setErrMsg(backMsg);
		checkByGrid.setSubDataList(subDataList);

		return checkByGrid;
	}


	//获取列表数据
	@Override
	@Transactional
	public Map<String,Object> getExtraParam( String bid) throws Exception{
		Map<String,Object> extraParam = new TlCustomsDeclaration()

				.addLeftJoin("YS_ProcInst P ON P.Bid = A.Bid ")
				.addField("FORMAT(P.UpdateTime, 'yyyy-MM-dd HH:mm:ss') AS auditingTime")//审核时间

				.setBid(bid)
				.queryFirstMap();
		return extraParam;
	}

}
